package br.com.fiap.mercadinho.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.fiap.mercadinho.bean.Product;
import br.com.fiap.mercadinho.common.DAOException;
import br.com.fiap.mercadinho.common.GenericDAOException;

public class ProductDAO extends GenericDAO {

	private final static String FIND_ALL_PRODUCT = " SELECT PRODUCT_ID,DESCRIPTION,PRICE" +
												 "    FROM PRODUCT                    "; 
	private final static String  FIND_PRODUCT_BY_ID = " SELECT PRODUCT_ID, " +
													  "        DESCRIPTION," +
													  "        PRICE       " +
													  "    FROM PRODUCT    " +
													  "  WHERE PRODUCT_ID = ?";
	
    private final static String  UPDATE_PRODUCT = " UPDATE PRODUCT SET    " +
    											  "      DESCRIPTION = ?, " +
    											  "      PRICE = ?        " +
    											  "    WHERE PRODUCT_ID = ?";
    
    private final static String  INSERT_PRODUCT = " INSERT INTO PRODUCT           " +
    											  " (PRODUCT_ID,DESCRIPTION,PRICE)" +
    											  " VALUES (0,?,?)";
    
    private final static String LAST_PRODUCT_ID = "SELECT LAST_INSERT_ID() LAST_ID "; 

	public List<Product> findAllProducts() throws DAOException {
		try {
			openConnection();
			PreparedStatement pst = conn.prepareStatement(FIND_ALL_PRODUCT);
			ResultSet rs = pst.executeQuery();
			List<Product> listProducts = new ArrayList<>();

			while (rs.next()) {
				listProducts.add(buildProduct(rs));
			}
			return listProducts;
		} catch (GenericDAOException | SQLException e) {
			throw new DAOException("Error in find product method: "
					+ e.getMessage());
		} finally {
			try {
				closeConnction();
			} catch (GenericDAOException e) {
				throw new DAOException("Error in close connection method: "
						+ e.getMessage());
			}
		}
	}

	public Product findProductById(Integer idProduct) throws DAOException {
		try {

			openConnection();
			PreparedStatement pst = conn.prepareStatement(FIND_PRODUCT_BY_ID);
			pst.setInt(1, idProduct);
			ResultSet rs = pst.executeQuery();

			if (rs.next())
				return buildProduct(rs);

		} catch (GenericDAOException | SQLException e) {
			throw new DAOException("Error in find product method: "
					+ e.getMessage());
		} finally {
			try {
				closeConnction();
			} catch (GenericDAOException e) {
				throw new DAOException("Error in close connection method: "
						+ e.getMessage());
			}
		}
		return null;
	}

	public void updateProduct(Product product) throws DAOException {
		try {
			openConnection();
			PreparedStatement pst = conn.prepareStatement(UPDATE_PRODUCT);
			pst.setString(1, product.getDescription());
			pst.setDouble(2, product.getPrice());
			pst.setInt(3, product.getId());
			pst.executeUpdate();
		} catch (GenericDAOException | SQLException e) {
			throw new DAOException("Error in update product method: "
					+ e.getMessage());
		} finally {
			try {
				closeConnction();
			} catch (GenericDAOException e) {
				throw new DAOException("Error in close connection method: "
						+ e.getMessage());
			}
		}
	}

	public void saveProduct(Product product) throws DAOException {
		try {
			openConnection();
			PreparedStatement pst = conn.prepareStatement(INSERT_PRODUCT);
			pst.setString(1, product.getDescription());
			pst.setDouble(2, product.getPrice());
			pst.executeUpdate();
			//recovery the last id save in the table of products
			pst = conn.prepareStatement(LAST_PRODUCT_ID);
			ResultSet rs = pst.executeQuery();
			if (rs.next())
				this.lastInsertId = rs.getInt("LAST_ID");
			
		} catch (GenericDAOException | SQLException e) {
			throw new DAOException("Error in insert product method: "
					+ e.getMessage());
		} finally {
			try {
				closeConnction();
			} catch (GenericDAOException e) {
				throw new DAOException("Error in close connection method: "
						+ e.getMessage());
			}
		}
	}
	
	public Integer getLastProductIdInserted() throws DAOException{
		try {

			openConnection();
			PreparedStatement pst = conn.prepareStatement(LAST_PRODUCT_ID);
			ResultSet rs = pst.executeQuery();

			if (rs.next())
				return rs.getInt("LAST_ID");

		} catch (GenericDAOException | SQLException e) {
			throw new DAOException("Error in find product method: "
					+ e.getMessage());
		} finally {
			try {
				closeConnction();
			} catch (GenericDAOException e) {
				throw new DAOException("Error in close connection method: "
						+ e.getMessage());
			}
		}
		return null;

	}

	private Product buildProduct(ResultSet rs) throws DAOException {
		Product product = new Product();
		try {
			product.setId(rs.getInt("PRODUCT_ID"));
			product.setDescription(rs.getString("DESCRIPTION"));
			product.setPrice(rs.getDouble("PRICE"));
			return product;
		} catch (SQLException e) {
			throw new DAOException("Error in build product method: "
					+ e.getMessage());
		}

	}
}
